import openpyxl,os
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill
import tkinter
from tkinter import filedialog

#在一个sheet页中，读取数据并按费用类型汇总费用
def readSheetData(workSheet, keyColName, valueColName, nameRowIdx, startRowIdx):
    #1.通过标题头转换成列编号
    #1.1标题行的数据，默认取数据的上一行，通过标题名称对比，获得列索引
    keyCol = -1
    valueCol = -1
    nameRowData = workSheet[nameRowIdx]
    for cell in nameRowData:
        #print(cell.column)
        if cell.value == keyColName:
            keyCol = cell.col_idx
        elif cell.value == valueColName:
            valueCol = cell.col_idx
    if keyCol==-1:
        print('sheet页"'+workSheet.title+'中"未找到"'+keyColName+'"标题行')
    if valueCol==-1:
        print('sheet页"'+workSheet.title+'中"未找到"'+valueColName+'"标题行')
    # TODO 异常抛出通过友好方式提醒用户
    #2.汇总数据
    sheetDate = {}
    for row in range(startRowIdx, workSheet.max_row + 1):
        key = workSheet.cell(column=keyCol, row=row).value
        if key is not None:
            value = workSheet.cell(column=valueCol, row=row).value
            sheetDate.setdefault(key, 0)
            sheetDate[key] += value
    return sheetDate

#比较
def compareData(sheetDate1, sheetDate2):
    result = {}
    #先比较sheetDate2的key
    for key2 in sheetDate2.keys():
        value2 = sheetDate2.get(key2, 0)
        value1 = sheetDate1.get(key2, 0)
        result[key2] = value2 - value1
    #再比较sheetDate2无，但sheetDate1有的key
    for key1 in sheetDate1.keys():
        if key1 not in sheetDate2.keys():
            value1 = sheetDate1.get(key1, 0)
            result[key1] = -value1
    return result

#创建新的sheet，写入原数据和差异
def writeSheetData(workbook, sheetName, fileName1, sheetData1, fileName2, sheetData2, diffData):
    sheet = workbook.create_sheet(sheetName)
    #标题行
    row = 1
    sheet['A' + str(row)].value = "服务目录"
    sheet['B' + str(row)].value = fileName1
    sheet['C' + str(row)].value = fileName2
    sheet['D' + str(row)].value = "差额"
    # 数据行
    for key in diffData.keys():
        row = row + 1
        sheet['A' + str(row)].value = key
        if key in sheetData1.keys():
            sheet['B' + str(row)].value = sheetData1[key]
        else:
            sheet['B' + str(row)].value = 0
        sheet['C' + str(row)].value = sheetData2[key]
        sheet['D' + str(row)].value = diffData[key]
    # 汇总行
    row+=1
    sheet['A' + str(row)].value = "合计"
    sheet['B' + str(row)].value = "=SUM(B2:B"+str(row-1)+")"
    sheet['C' + str(row)].value = "=SUM(C2:C"+str(row-1)+")"
    sheet['D' + str(row)].value = "=C"+str(row)+"-B"+str(row)
    # 统一格式化单元格
    sheet.column_dimensions['A'].width = 40
    sheet.column_dimensions['B'].width = 40
    sheet.column_dimensions['C'].width = 40
    sheet.column_dimensions['D'].width = 40
    max_column_letter = openpyxl.utils.get_column_letter(sheet.max_column)
    for sheetRow in sheet['A1':max_column_letter+str(sheet.max_row)]:
        for cell in sheetRow:
            cellFormat(cell)


# 格式化单元格
def cellFormat(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'))

# 比较
def doCompare():
    # 1.读取账单数据
    file1 = entryFilePath1.get()
    file2 = entryFilePath2.get()
    fileName1 = os.path.basename(file1)
    fileName2 = os.path.basename(file2)
    wb1 = openpyxl.load_workbook(file1)
    wb2 = openpyxl.load_workbook(file2)
    # 比较配置：对比sheet页名称，对比key列名称，对比value列名称，标题头所在行号，数据开始行号
    sets = [['汇总','费用类别','含税金额（元）', 8, 10],
            ['按量计费', '三级目录', '账单费用(含税,元)', 2, 3],
            ['项目包干', '项目名称', '账单费用(含税,元)', 2, 3],
            ['成本核算', '项目名称', '账单费用(含税,元)', 2, 3]]

    # TODO 可以考虑在比较数据前，校验前置到此

    # 2.比较账单数据,同步输出比较结果
    wb3 = openpyxl.Workbook()
    for set in sets:
        sheetName = set[0]
        sheetData1 = readSheetData(wb1[sheetName], set[1], set[2], set[3], set[4])
        sheetData2 = readSheetData(wb2[sheetName], set[1], set[2], set[3], set[4])
        diffData = compareData(sheetData1, sheetData2)
        writeSheetData(wb3, sheetName, fileName1, sheetData1, fileName2, sheetData2, diffData)
    # 删除旧的比对数据
    for filename in os.listdir():
        if filename=='SLA账单比对.xlsx':
            os.unlink(filename)
    wb3.save('SLA账单比对.xlsx')

def get_directories1():
    path = tkinter.filedialog.askopenfilename()
    filePath1.set(path)

def get_directories2():
    path = tkinter.filedialog.askopenfilename()
    filePath2.set(path)

if __name__=='__main__':
    root = tkinter.Tk()
    root.geometry("1000x800")
    root.title("账单文件比对")
    root.columnconfigure(0, weight=1)
    root.rowconfigure(0, weight=1)

    mainFrame = tkinter.Frame(root)
    mainFrame.grid(column=0, row=0, sticky=(tkinter.N, tkinter.W, tkinter.E, tkinter.S))

    labelFile1 = tkinter.Label(mainFrame, text="对比文件1：")
    labelFile1.grid(column=1, row=1)

    filePath1 = tkinter.StringVar()
    entryFilePath1 = tkinter.Entry(mainFrame, width=80, textvariable=filePath1)
    entryFilePath1.grid(column=2, row=1, sticky=(tkinter.W, tkinter.E))

    buttonSelectFile1 = tkinter.Button(mainFrame, text='选持目录', command=get_directories1)
    buttonSelectFile1.grid(column=3, row=1)

    labelFile2 = tkinter.Label(mainFrame, text="对比文件2：")
    labelFile2.grid(column=1, row=2)

    filePath2 = tkinter.StringVar()
    entryFilePath2 = tkinter.Entry(mainFrame, width=80, textvariable=filePath2)
    entryFilePath2.grid(column=2, row=2, sticky=(tkinter.W, tkinter.E))

    buttonSelectFile2 = tkinter.Button(mainFrame, text='选持目录', command=get_directories2)
    buttonSelectFile2.grid(column=3, row=2)

    buttonCompare = tkinter.Button(mainFrame, text="比较", command=doCompare)
    buttonCompare.grid(column=1, row=3)
    buttonExit = tkinter.Button(mainFrame, text="退出", command=root.quit)
    buttonExit.grid(column=2, row=3)

    for child in mainFrame.winfo_children():
        child.grid_configure(padx=5, pady=5)

    tkinter.mainloop()








